﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DeqiYQ.SQLConn;

namespace DeqiYQ.Controllers
{
    public class DataViewController : Controller
    {
        BaseDao dao = new BaseDao();
        // GET: DataView
        public ActionResult Index()
        {
            string Sql, Sql2;
            Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,b.amp1,b.amp2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                " where a.IsDelete=0";
            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "lon", "lat", "Y1", "V1", "K1", "Y2", "V2", "K2", "LastTime","amp1","amp2" });
            ViewBag.DeviceDataJson = Newtonsoft.Json.JsonConvert.SerializeObject(DeviceData);
            ViewBag.DeviceData = DeviceData;


            Dictionary<string, object> wetherMap = Weather.GetWeater();

            //统计在线离线设备            
            Sql = "select count(*) as TotalNum from t_o_dataTemp";
            Sql2 = " select count(*) as TotalNum from t_o_dataTemp where DataTime>DATEADD (hour,-1,GETDATE())";
            var ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int SumNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            ListNum = dao.GetList(Sql2, new string[] { "TotalNum" });
            int OnLineNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            Sql = "select count(MN) as TotalNum from t_o_dataTemp where (Y1>1 or V1>15 or Y2>1 or V2>15)";
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int ExcessNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            ViewBag.SumNum = SumNum;
            ViewBag.OnLineNum = OnLineNum;
            ViewBag.OffLineNum = SumNum - OnLineNum;
            ViewBag.ExcessNum = ExcessNum;

            return View("BigData");
        }

        public ActionResult BigData2()
        {
            string Sql, Sql2;
            Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,b.amp1,b.amp2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                " where a.IsDelete=0";
            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "lon", "lat", "Y1", "V1", "K1", "Y2", "V2", "K2", "LastTime", "amp1", "amp2" });
            ViewBag.DeviceDataJson = Newtonsoft.Json.JsonConvert.SerializeObject(DeviceData);
            ViewBag.DeviceData = DeviceData;


            Dictionary<string, object> wetherMap = Weather.GetWeater();
            ViewBag.WetherMap = wetherMap;

            //统计在线离线设备            
            Sql = "select count(*) as TotalNum from t_o_dataTemp";
            Sql2 = " select count(*) as TotalNum from t_o_dataTemp where DataTime>DATEADD (hour,-1,GETDATE())";
            var ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int SumNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            ListNum = dao.GetList(Sql2, new string[] { "TotalNum" });
            int OnLineNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            Sql = "select count(MN) as TotalNum from t_o_dataTemp where (Y1>1 or V1>15 or Y2>1 or V2>15)";
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int ExcessNum = int.Parse(ListNum[0]["TotalNum"].ToString());
            ViewBag.SumNum = SumNum;
            ViewBag.OnLineNum = OnLineNum;
            ViewBag.OffLineNum = SumNum - OnLineNum;
            ViewBag.ExcessNum = ExcessNum;

            //浓度超标单位
            string StartTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:00:00");
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd HH:00:00");
            string sql = "select A.MN,A.Name,t.Y1,t.Y1*100 as percentage,t.DataTime FROM t_d_Device A INNER JOIN";
            sql += "(";
            sql += "SELECT row_number() over (partition by mn order by Y1 DESC) AS KEYID, * FROM t_o_data where DataTime  BETWEEN '" + StartTime + "' and '" + EndTime + "' and Y1>1";
            sql += ") as t ON A.MN=T.MN where t.KEYID=1";
            List<Dictionary<string, object>> ListData = new List<Dictionary<string, object>>();
            ListData = dao.GetList(sql, new string[] { "MN", "Name", "Y1", "percentage", "DataTime" });
            ViewBag.ExcessList = ListData;


            return View("BigData_2");
        }

        public ActionResult Main()
        {
            //首先验证用户
            
            if (Request.Cookies["UserId"] == null)
            {
                //删除cookies
                HttpCookie aCookie;
                string cookieName;
                int limit = Request.Cookies.Count;
                for (int i = 0; i < limit; i++)
                {
                    cookieName = Request.Cookies[i].Name;
                    aCookie = new HttpCookie(cookieName);
                    aCookie.Expires = DateTime.Now.AddDays(-1);
                    Response.Cookies.Add(aCookie);
                }
                return View("H5Login");
            }
            string UserId = Request.Cookies["UserId"].Value == null ? null : Request.Cookies["UserId"].Value;
            string Sql = "select count(*) as TotalNum from t_d_User where UserId=" + UserId;
            string Sql2 = "";
            List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            if (1 != int.Parse(ListNum[0]["TotalNum"].ToString()))
            {

                //删除cookies
                HttpCookie aCookie;
                string cookieName;
                int limit = Request.Cookies.Count;
                for (int i = 0; i < limit; i++)
                {
                    cookieName = Request.Cookies[i].Name;
                    aCookie = new HttpCookie(cookieName);
                    aCookie.Expires = DateTime.Now.AddDays(-1);
                    Response.Cookies.Add(aCookie);
                }
                return View("H5Login");
            }

            string SearchType = Request.QueryString["SearchType"] ?? null;
            string SqlCondition = "";
            if (SearchType == null)     //默认查找在线设备
            {
                SqlCondition = " AND DataTime>DATEADD (mi,-10,GETDATE()) ";
            }
            if (SearchType == "OffLine")    //查找理线设备
            {
                SqlCondition = " AND DataTime<DATEADD (mi,-10,GETDATE()) ";
            }
            //下面拉取设备信息
            //先判断权限
            Sql = "select * from t_d_user where UserId=" + UserId;
            Dictionary<string, object> UserInfo = new Dictionary<string, object>();
            UserInfo = dao.GetList(Sql, new string[] { "UserId", "NickName", "Authority" })[0];
            ViewBag.NickName = UserInfo["NickName"].ToString();
            string Authority = UserInfo["Authority"].ToString();
            if (Authority =="1")    //表示所有用户设备
            {

                Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2 from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                    " where a.IsDelete=0" + SqlCondition;              
            }

            if(Authority == "2")    //表示单独用户设备
            {
                Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2 from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                    " left join t_o_UserDevice c on c.DeviceId=a.DeviceId where a.IsDelete=0 and c.UserId=" + UserId + SqlCondition;
            }

            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "Y1", "V1", "K1","Y2","V2","K2" });
            ViewBag.DeviceData = DeviceData;

            //统计联网点等数据
            if (Authority == "1")
            {
                Sql = "select count(*) as TotalNum from t_d_Device";
                Sql2 = " select count(*) as TotalNum from t_o_dataTemp where DataTime>DATEADD (mi,-10,GETDATE())";


            }
            if (Authority == "2")
            {
                Sql = "select count(*) as TotalNum from t_o_UserDevice where UserId=" + UserId;
                Sql2 = " SELECT COUNT(*) as TotalNum  FROM t_o_dataTemp a INNER JOIN t_d_Device b ON b.MN=a.MN " +
                    " INNER JOIN t_o_UserDevice c ON b.DeviceId=c.DeviceId AND c.UserId=" + UserId +
                    " where DataTime>DATEADD (mi,-10,GETDATE())";
            }
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int SumNum= int.Parse(ListNum[0]["TotalNum"].ToString());

            ListNum=dao.GetList(Sql2, new string[] { "TotalNum" });
            int OnLineNum= int.Parse(ListNum[0]["TotalNum"].ToString());

            ViewBag.SumNum = SumNum;
            ViewBag.OnLineNum = OnLineNum;
            ViewBag.OffLineNum = SumNum - OnLineNum;

            return View("MainPage");
        }

        public ActionResult GetSingleAvg()
        {
            string MN = Request.Form["MN"].ToString();
            string StartTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:00:00");
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd HH:00:00");
            Dictionary<string, object> Map = new Dictionary<string, object>();

            string sql = "BEGIN " +
                    " declare @startDate datetime" +
                    " declare @endDate datetime" +
                    " SELECT @startDate = '" + StartTime + "', @endDate = '" + EndTime + "'" +
                    " ; WITH tb AS(" +
                    " SELECT @startDate AS 'DateHour'" +
                    " UNION ALL" +
                    " SELECT DATEADD(HOUR, 1, DateHour) FROM tb WHERE DateHour < @endDate" +
                    " )" +
                    " SELECT DATEPART(HOUR, a.datehour) as DateHour, round(AVG(b.Y1), 2) as Y1,round(AVG(b.V1), 2) as V1,round(AVG(b.K1), 2) as K1," +
                    " round(AVG(b.Y2), 2) as Y2,round(AVG(b.V2), 2) as V2,round(AVG(b.K2), 2) as K2" +
                    " from tb a LEFT JOIN t_o_data b ON" +
                    " CONVERT(VARCHAR(100), a.DateHour, 112) = CONVERT(VARCHAR(100), b.DataTime, 112)" +
                    " AND DATEPART(HOUR, a.datehour)= DATEPART(hour, b.DataTime) AND b.Y1 > 0 AND b.MN='" + MN + "'" +
                    " GROUP BY datehour" +
                    " ORDER BY a.datehour ASC" +
                    " END";
            List<Dictionary<string, object>> ListData = new List<Dictionary<string, object>>();
            ListData = dao.GetList(sql, new string[] { "DateHour", "Y1", "V1", "K1", "Y2", "V2", "K2" });
            return Json(ListData);



        }


        public ActionResult ShopDetail()
        {
            string MN = Request.QueryString["MN"] ?? null;
            string Sql = "";
            Sql = "select a.DeviceId,a.MN,a.Name,a.Address,ChannelNum,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,b.amp1,b.amp2,CONVERT(varchar(100), datatime, 120) as lastConnTime,b.DYDY,b.DYDL from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                " where a.MN='" + MN + "'";
            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "Y1", "V1", "K1", "Y2", "V2", "K2","amp1","amp2", "lastConnTime","DYDY","DYDL" });
            ViewBag.DeviceDataMap = DeviceData[0];

            return View("ShopDetail");
        }

        public ActionResult Record()
        {
            string MN = Request.QueryString["MN"] ?? null;
            string sql = "SELECT Y1,V1,K1,Y2,V2,K2,CONVERT(VARCHAR(100),dataTime,120) as DataTime FROM t_o_data where MN='" + MN + "'" +
                " and (Y1>1 OR V1>15 or Y2>1 or V2>15) ORDER BY DataTime DESC";
            var DataList = dao.GetList(sql, new string[] { "Y1", "V1", "K1", "Y2", "V2", "K2", "DataTime" });
            sql = "select * from t_d_Device where MN='" + MN + "'";
            var DeviceMap = dao.GetList(sql, new string[] { "Name", "Address"})[0];

            ViewBag.DataList = DataList;
            ViewBag.DeviceMap = DeviceMap;
            ViewBag.MN = MN;
            return View("Record");
        }

        public ActionResult History()
        {
            string MN = Request.QueryString["MN"].ToString();
            string sql = "select Name,Address from t_d_Device where MN='" + MN + "'";
            var Map = dao.GetList(sql, new string[] { "Name", "Address" })[0];
            ViewBag.Map = Map;
            ViewBag.MN = MN;

            return View("History");
        }

        public ActionResult GetDataByTime()
        {
            string StartTime = Request.Form["StartTime"].ToString();
            string ChannelNum= Request.Form["ChannelNum"].ToString();
            string MN = Request.Form["MN"].ToString();
            string Sql = "";
            if (ChannelNum == "1")
            {
                Sql = "SELECT Y1,V1,K1,CONVERT(VARCHAR(100), DataTime, 108) as DataTime from t_o_data " +
                    " where MN='" + MN + "' and  DataTime BETWEEN '" + StartTime + " 00:00:00' AND  '" + StartTime + " 23:59:59' " +
                    " order by DataTime Desc";
            }
            else
            {
                Sql = "SELECT Y2 as Y1,V2 as V1,K2 as K1,CONVERT(VARCHAR(100), DataTime, 108) as DataTime from t_o_data " +
                    " where MN='" + MN + "' and  DataTime BETWEEN '" + StartTime + " 00:00:00' AND  '" + StartTime + " 23:59:59' " +
                    " order by DataTime Desc";
            }

            var DataList = dao.GetList(Sql, new string[] { "Y1", "V1", "K1", "DataTime" });


            return Json(DataList);
        }


        public ActionResult MapDetail()
        {

            if (Request.Cookies["UserId"] == null)
            {
                //删除cookies
                HttpCookie aCookie;
                string cookieName;
                int limit = Request.Cookies.Count;
                for (int i = 0; i < limit; i++)
                {
                    cookieName = Request.Cookies[i].Name;
                    aCookie = new HttpCookie(cookieName);
                    aCookie.Expires = DateTime.Now.AddDays(-1);
                    Response.Cookies.Add(aCookie);
                }
                return View("H5Login");
            }
            string UserId = Request.Cookies["UserId"].Value == null ? null : Request.Cookies["UserId"].Value;
            string Sql = "select count(*) as TotalNum from t_d_User where UserId=" + UserId;
            string Sql2 = "";
            List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            if (1 != int.Parse(ListNum[0]["TotalNum"].ToString()))
            {

                //删除cookies
                HttpCookie aCookie;
                string cookieName;
                int limit = Request.Cookies.Count;
                for (int i = 0; i < limit; i++)
                {
                    cookieName = Request.Cookies[i].Name;
                    aCookie = new HttpCookie(cookieName);
                    aCookie.Expires = DateTime.Now.AddDays(-1);
                    Response.Cookies.Add(aCookie);
                }
                return View("H5Login");
            }

            string SearchType = Request.QueryString["SearchType"] ?? null;
            string SqlCondition = "";
            if (SearchType == null)     //默认查找在线设备
            {
                SqlCondition = " AND DataTime>DATEADD (hour,-1,GETDATE()) ";
            }
            if (SearchType == "OffLine")    //查找理线设备
            {
                SqlCondition = " AND DataTime<DATEADD (hour,-1,GETDATE()) ";
            }
            //下面拉取设备信息
            //先判断权限
            Sql = "select * from t_d_user where UserId=" + UserId;
            Dictionary<string, object> UserInfo = new Dictionary<string, object>();
            UserInfo = dao.GetList(Sql, new string[] { "UserId", "NickName", "Authority" })[0];
            ViewBag.NickName = UserInfo["NickName"].ToString();
            string Authority = UserInfo["Authority"].ToString();
            if (Authority == "1")    //表示所有用户设备
            {

                Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.lon,a.lat,b.Y1,b.V1,b.K1 from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                    " where a.IsDelete=0" + SqlCondition;
            }

            if (Authority == "2")    //表示单独用户设备
            {
                Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.lon,a.lat,b.Y1,b.V1,b.K1 from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                    " left join t_o_UserDevice c on c.DeviceId=a.DeviceId where a.IsDelete=0 and c.UserId=" + UserId + SqlCondition;
            }

            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address","lon","lat", "Y1", "V1", "K1" });
            ViewBag.DeviceDataJson = Newtonsoft.Json.JsonConvert.SerializeObject(DeviceData);

            //统计联网点等数据
            if (Authority == "1")
            {
                Sql = "select count(*) as TotalNum from t_d_Device";
                Sql2 = " select count(*) as TotalNum from t_o_dataTemp where DataTime>DATEADD (hour,-1,GETDATE())";


            }
            if (Authority == "2")
            {
                Sql = "select count(*) as TotalNum from t_o_UserDevice where UserId=" + UserId;
                Sql2 = " SELECT COUNT(*) as TotalNum  FROM t_o_dataTemp a INNER JOIN t_d_Device b ON b.MN=a.MN " +
                    " INNER JOIN t_o_UserDevice c ON b.DeviceId=c.DeviceId AND c.UserId=" + UserId +
                    " where DataTime>DATEADD (hour,-1,GETDATE())";
            }
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int SumNum = int.Parse(ListNum[0]["TotalNum"].ToString());

            ListNum = dao.GetList(Sql2, new string[] { "TotalNum" });
            int OnLineNum = int.Parse(ListNum[0]["TotalNum"].ToString());

            ViewBag.SumNum = SumNum;
            ViewBag.OnLineNum = OnLineNum;
            ViewBag.OffLineNum = SumNum - OnLineNum;









            return View("MapDetail");
        }

        public ActionResult H5Login()
        {
            return View("H5Login");
        }

        public ActionResult LogOut()
        {
            //删除cookies
            HttpCookie aCookie;
            string cookieName;
            int limit = Request.Cookies.Count;
            for (int i = 0; i < limit; i++)
            {
                cookieName = Request.Cookies[i].Name;
                aCookie = new HttpCookie(cookieName);
                aCookie.Expires = DateTime.Now.AddDays(-1);
                Response.Cookies.Add(aCookie);
            }

            return View("H5Login");
        }

        public ActionResult LoginCheck()
        {
            string UserName = Request.Form["UserName"] == null ? null : Request.Form["UserName"].ToString().Replace(" ", "");
            string PassWord = Request.Form["PassWord"] == null ? null : Request.Form["PassWord"].ToString().Replace(" ", "");
            string Sql = "select count(UserId) as TotalNum FROM t_d_user where IsDelete=0 and UserName='" + UserName + "' and PassWord='" + PassWord + "'";
            List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
            Dictionary<string, object> result = new Dictionary<string, object>();
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            if (1 != int.Parse(ListNum[0]["TotalNum"].ToString()))
            {
                ViewBag.state = "LoginError";

                //删除cookies
                HttpCookie aCookie;
                string cookieName;
                int limit = Request.Cookies.Count;
                for (int i = 0; i < limit; i++)
                {
                    cookieName = Request.Cookies[i].Name;
                    aCookie = new HttpCookie(cookieName);
                    aCookie.Expires = DateTime.Now.AddDays(-1);
                    Response.Cookies.Add(aCookie);
                }
                result.Add("result", "账号或密码错误");
                return Json(result);
                
            }
            else
            {
                Sql = "select UserID from t_d_user where UserName='" + UserName + "' and PassWord='" + PassWord + "'";
                List<Dictionary<string, object>> UserList = new List<Dictionary<string, object>>();
                UserList = dao.GetList(Sql, new string[] { "UserId" });
                Response.Cookies["UserId"].Value = UserList[0]["UserId"].ToString();
                Response.Cookies["UserId"].Expires = DateTime.Now.AddMonths(1);
                result.Add("result", "OK");
                return Json(result);
            }
        }






    }
}